<?php
function percent($nb,$total) {
	return ($total == 0 ? 0 : round($nb*100/$total,2))."%";
}
function query_applicants_score_for_rule($rule,$rule_subjects,$program_id,$additional_fields=null) {
	$q = "SELECT SUM(score_subject_exam) AS total_score";
	if ($additional_fields<>null)
		foreach ($additional_fields as $name=>$alias)
			$q .= ",".$name." AS ".$alias;
	$q .= " FROM applicant";
	$q .= " LEFT JOIN applicant_score_subject_exam ON applicant_score_subject_exam.applicant_id=applicant.applicant_id";
	$q .= " WHERE score_subject_exam IS NOT NULL AND selection_campaign_id=".$_SESSION["campaign_id"];
	if ($rule["apply_to_high_school_id"] <> null) 
		$q .= " AND applicant.high_school_id=".$rule["apply_to_high_school_id"];
	else {
		$excluded = get_excluded_high_schools($rule, $rule_subjects, $program_id);
		if (count($excluded)>0) {
			$s = "";
			foreach ($excluded as $hs_id=>$hs_name) {
				if ($s<>"") $s.=" AND ";
				$s .= "applicant.high_school_id<>".$hs_id;
			}
			$q .= " AND (applicant.high_school_id IS NULL OR (".$s."))";
		}
	}
	if ($rule["apply_to_ngo_id"] <> null) 
		$q .= " AND applicant.ngo_id=".$rule["apply_to_ngo_id"];
	else {
		$excluded = get_excluded_ngos($rule, $rule_subjects, $program_id);
		if (count($excluded)>0) {
			$s = "";
			foreach ($excluded as $ngo_id=>$ngo_name) {
				if ($s<>"") $s.=" AND ";
				$s .= "applicant.ngo_id<>".$ngo_id;
			}
			$q .= " AND (applicant.ngo_id IS NULL OR (".$s."))";
		}
	}
	$q .= " AND (";
	for ($i = 0; $i < count($rule_subjects); $i++) {
		if ($i > 0) $q .= " OR ";
		$q .= "subject_id=".$rule_subjects[$i]["subject_id"];
	}
	$q .= ") GROUP BY applicant.applicant_id";
	return $q;
}
function calculate_avg_min_max($rule,$rule_subjects,$program_id) {
	$q = "SELECT AVG(total_score),MIN(total_score),MAX(total_score),COUNT(total_score) FROM (";
	$q .= query_applicants_score_for_rule($rule,$rule_subjects,$program_id);
	$q .= ") AS applicant_score";
	$res = DataBase::query($q);
	if ($res === false || count($res)==0) return false;
        return array(round($res[0][0],2),round($res[0][1],2),round($res[0][2],2),$res[0][3]);
}
function nb_applicants_satisfying_rule($rule,$rule_subjects,$program_id) {
	$q = "SELECT COUNT(total_score) FROM (";
	$q .= query_applicants_score_for_rule($rule,$rule_subjects,$program_id);
	$q .= ") AS applicant_score";
	$q .= " WHERE applicant_score.total_score >= ".$rule["mark"];
	$res = DataBase::query($q);
	if ($res === false || count($res)==0) return 0;
	return $res[0][0];
}
function nb_applicants_eligible_for_program($program_id,$rules,$rules_subjects,$rules_type=-1) {
	if (count($rules)==0) return "No rule";
	$q = "SELECT COUNT(*) FROM ".query_eligibles_for_program($program_id,$rules,$rules_subjects,$rules_type);
	$res = DataBase::query($q);
	if ($res == false || count($res)==0) return 0;
	return $res[0][0];
}
function same_subjects($list1, $list2) {
	if (count($list1)<>count($list2)) return false;
	foreach ($list1 as $s1) {
		$found = false;
		foreach ($list2 as $s2) if ($s1["subject_id"]==$s2["subject_id"]) { $found = true; break; }
		if (!$found) return false;
	}
	return true;
}
function query_eligibles_for_program($program_id,$rules,$rules_subjects,$rules_type=-1) {
	// first extract general rules without exception, associate other general rules with exceptions, and extract exceptions without general rule
	$general_min_rules = array();
	$general_ts_rules = array();
	$exception_no_general = array();
	$exceptions_min = array();
	$exceptions_ts = array();
	$remaining_rules = array();
	foreach ($rules as $rule) {
		if ($rules_type >= 0 && $rule["type"] <> $rules_type) continue;
		array_push($remaining_rules, $rule);
	}
	while (count($remaining_rules)>0) {
		$rule = $remaining_rules[0];
		array_splice($remaining_rules, 0, 1);
		$hs_id = $rule["apply_to_high_school_id"];
		$ngo_id = $rule["apply_to_ngo_id"];
		$subjects = $rules_subjects[$rule["exam_eligibility_rule_id"]];
		if ($hs_id == null && $ngo_id == null) {
			// general rule: check if matching exceptions
			$matches = array();
			for ($i = 0; $i < count($remaining_rules); $i++) {
				$r = $remaining_rules[$i];
				$r_subjects = $rules_subjects[$r["exam_eligibility_rule_id"]];
				if ($r["type"]<>$rule["type"]) continue;
				if (!same_subjects($subjects,$r_subjects)) continue;
				array_push($matches, $remaining_rules[$i]);
				array_splice($remaining_rules, $i, 1);
				$i--;
			}
			if ($rule["type"] == 0) {
				if (count($matches)==0)
					array_push($general_min_rules, $rule);
				else
					array_push($exceptions_min, array($rule, $matches));
			} else {
				if (count($matches)==0)
					array_push($general_ts_rules, $rule);
				else
					array_push($exceptions_ts, array($rule, $matches));
			}
		} else {
			// exception rule: search for general rule + other similar exceptions
			$general = null;
			$matches = array();
			for ($i = 0; $i < count($remaining_rules); $i++) {
				$r = $remaining_rules[$i];
				$r_subjects = $rules_subjects[$r["exam_eligibility_rule_id"]];
				if ($r["type"]<>$rule["type"]) continue;
				if (!same_subjects($subjects,$r_subjects)) continue;
				if ($r["apply_to_high_school_id"]==null && $r["apply_to_ngo_id"]==null)
					$general = $r;
				else
					array_push($matches, $r);
				array_splice($remaining_rules, $i, 1);
				$i--;
			}
			if ($general <> null) {
				array_push($matches, $rule);
				if ($rule["type"]==0)
					array_push($exceptions_min, array($general, $matches));
				else
					array_push($exceptions_ts, array($general, $matches));
			} else {
				array_push($matches, $rule);
				array_push($exception_no_general, $matches);
			}
		}
	}

	$q = "";

	// 1- all general rules without any exception
	$first_select = true;
	$general_rules = array_merge($general_min_rules, $general_ts_rules);
	if (count($general_rules)>0) {
		$i = 0;
		foreach ($general_rules as $rule) {
			if ($i > 0) $q .= " JOIN ";
			$q .= "(";
			$q .= query_applicants_score_for_rule($rule, $rules_subjects[$rule["exam_eligibility_rule_id"]],$program_id,array("applicant.applicant_id"=>($first_select ? "APPLICANT_ID" : "gr_".$i."_applicant_id"),"applicant.sex"=>($first_select ? "SEX" : "gr_".$i."_sex"),"applicant.ngo_id"=>($first_select ? "NGO_ID" : "gr_".$i."_ngo_id")));
			$first_select = false;
			$q .= ") AS gr_".$i;
			if ($i > 0) $q .= " ON gr_".$i."_applicant_id=APPLICANT_ID";
			$i++;
		}
	}
	// 2- union between general rule and exceptions
	$exceptions = array_merge($exceptions_min, $exceptions_ts);
	if (count($exceptions)>0) {
		$i = 0;
		foreach ($exceptions as $exception) {
			if ($i > 0 || count($general_rules)>0) $q .= " JOIN ";
			$gr = $exception[0];
			$er_list = $exception[1];
			$q .= "(";
			$q .= query_applicants_score_for_rule($gr, $rules_subjects[$gr["exam_eligibility_rule_id"]],$program_id,array("applicant.applicant_id"=>($first_select ? "APPLICANT_ID" : "egr_".$i."_applicant_id"),"applicant.high_school_id"=>"egr_".$i."_hs","applicant.ngo_id"=>"egr_".$i."_ngo"));
			foreach ($er_list as $er) {
				$q .= " UNION ";
				$q .= query_applicants_score_for_rule($er, $rules_subjects[$gr["exam_eligibility_rule_id"]],$program_id,array("applicant.applicant_id"=>($first_select ? "APPLICANT_ID" : "egr_".$i."_applicant_id"),"applicant.high_school_id"=>"egr_".$i."_hs","applicant.ngo_id"=>"egr_".$i."_ngo"));
			}
			$q .= ") AS egr_".$i;
			if (!$first_select) $q .= " ON egr_".$i."_applicant_id=APPLICANT_ID";
			$first_select = false;
			$i++;
		}
	}
	// TODO: exceptions without general rules
	
	$q .= " WHERE ";
	$min = "";
	$ts = "";
	if (count($general_rules)>0) {
		$i = 0;
		foreach ($general_rules as $rule) {
			if ($rule["type"] == 0) {
				if ($min<>"") $min .= " AND ";
				$min .= "gr_".$i.".total_score >= ".$rule["mark"];
			} else {
				if ($ts<>"") $ts .= " OR ";
				$ts .= "gr_".$i.".total_score >= ".$rule["mark"];
			}
			$i++;
		}
	}
	if (count($exceptions)>0) {
		$i = 0;
		foreach ($exceptions as $exception) {
			$gr = $exception[0];
			$er_list = $exception[1];
			$cd = "";
			$s = "";
			foreach ($er_list as $er) if ($er["apply_to_high_school_id"]) $s .= " OR egr_".$i."_hs<>".$er["apply_to_high_school_id"];
			if ($s<>"")
				$cd .= "(egr_".$i."_hs IS NULL".$s.")";
			$s = "";
			foreach ($er_list as $er) if ($er["apply_to_ngo_id"]) $s .= " OR egr_".$i."_ngo<>".$er["apply_to_ngo_id"];
			if ($s<>"") {
				if ($cd<>"") $cd .= " AND ";
				$cd .= "(egr_".$i."_ngo IS NULL".$s.")";
			}
			if ($cd<>"") $cd .= " AND ";
			$cd .= "egr_".$i.".total_score>=".$gr["mark"];
			$cd = "(".$cd.")";
			
			foreach ($er_list as $er) {
				$cd_er = "";
				if ($er["apply_to_high_school_id"]<>null) $cd_er = "egr_".$i."_hs=".$er["apply_to_high_school_id"];
				if ($er["apply_to_ngo_id"]<>null) {
					if ($cd_er<>"") $cd_er .= " AND ";
					$cd_er .= "egr_".$i."_ngo=".$er["apply_to_ngo_id"];
				}
				if ($cd_er<>"") $cd_er .= " AND ";
				$cd_er .= "egr_".$i.".total_score>=".$er["mark"];
				$cd .= " OR (".$cd_er.")";
			}
			$cd = "(".$cd.")";
			
			if ($rule["type"] == 0) {
				if ($min<>"") $min .= " AND ";
				$min .= $cd;
			} else {
				if ($ts<>"") $ts .= " OR ";
				$ts .= $cd;
			}
			$i++;
		}
	}
	// TODO exceptions without general
	$q .= $min;
	if ($min<>"" && $ts<>"") $q .= " AND ";
	if ($ts<>"") $q .= "(".$ts.")";
	return $q;
}
function query_eligibles_for_program_and_not_for_others($program_id,$other_programs_id,$program_rules,$rules_subjects) {
	$q = "SELECT APPLICANT_ID,SEX,NGO_ID FROM (SELECT APPLICANT_ID,SEX,NGO_ID FROM ".query_eligibles_for_program($program_id, $program_rules[$program_id], $rules_subjects);
	$q .= ") AS eroot WHERE eroot.APPLICANT_ID NOT IN (";
	$first = true;
	foreach ($other_programs_id as $id) {
		if ($first) $first = false; else $q.=" UNION ";
		$q .= "SELECT APPLICANT_ID FROM ".query_eligibles_for_program($id, $program_rules[$id], $rules_subjects);
	}
	$q .= ")";
	return $q;
}
function get_excluded_high_schools($rule,$rule_subjects,$program_id) {
	if (count($rule_subjects)==0) return array();
	$q = "SELECT * FROM exam_eligibility_rule";
	for ($i = 0; $i < count($rule_subjects); $i++)
		$q .= " LEFT JOIN exam_eligibility_rule_subjects AS rs".$i." ON (rs".$i.".exam_eligibility_rule_id=exam_eligibility_rule.exam_eligibility_rule_id AND rs".$i.".subject_id=".$rule_subjects[$i]["subject_id"].")";
	$q .= " LEFT JOIN high_school ON high_school.high_school_id=exam_eligibility_rule.apply_to_high_school_id";
	$q .= " WHERE apply_to_high_school_id IS NOT NULL AND program_id=".$program_id." AND selection_campaign_id=".$_SESSION["campaign_id"];
	for ($i = 0; $i < count($rule_subjects); $i++)
		$q .= " AND rs".$i.".subject_id IS NOT NULL";
	$res = DataBase::query($q);
	$a = array();
	if ($res == false) return $a;
	foreach ($res as $r) $a[$r["apply_to_high_school_id"]] = $r["high_school_name"];
	return $a;
}
function get_excluded_ngos($rule,$rule_subjects,$program_id) {
	if (count($rule_subjects)==0) return array();
	$q = "SELECT * FROM exam_eligibility_rule";
	for ($i = 0; $i < count($rule_subjects); $i++)
		$q .= " LEFT JOIN exam_eligibility_rule_subjects AS rs".$i." ON (rs".$i.".exam_eligibility_rule_id=exam_eligibility_rule.exam_eligibility_rule_id AND rs".$i.".subject_id=".$rule_subjects[$i]["subject_id"].")";
	$q .= " LEFT JOIN ngo ON ngo.ngo_id=exam_eligibility_rule.apply_to_ngo_id";
	$q .= " WHERE apply_to_ngo_id IS NOT NULL";
	for ($i = 0; $i < count($rule_subjects); $i++)
		$q .= " AND rs".$i.".subject_id IS NOT NULL AND program_id=".$program_id." AND selection_campaign_id=".$_SESSION["campaign_id"];
	$res = DataBase::query($q);
	$a = array();
	if ($res == false) return $a;
	foreach ($res as $r) $a[$r["apply_to_ngo_id"]] = $r["ngo_name"];
	return $a;
}
function query_applicants_having_all_marks($subjects) {
	$query = "SELECT applicant.applicant_id FROM applicant";
	$query .= " LEFT JOIN applicant_more_info ON applicant_more_info.applicant_id=applicant.applicant_id";
	for ($i = 0; $i < count($subjects); $i++)
		$query .= " LEFT JOIN applicant_score_subject_exam AS score_".$i." ON (score_".$i.".applicant_id=applicant.applicant_id AND score_".$i.".subject_id=".$subjects[$i]["subject_id"].")";
	$query .= " WHERE selection_campaign_id=".$_SESSION["campaign_id"]." AND applicant_more_info_attendee_exam_status=1";
	for ($i = 0; $i < count($subjects); $i++)
		$query .= " AND score_".$i.".score_subject_exam IS NOT NULL";
	return $query;
}
?>